﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using GtsServer.Common;

namespace GtsServer.DAL
{
    /// <summary>
    /// 数据访问类:Prize
    /// </summary>
    public partial class PrizeDAL
    {
        public PrizeDAL()
        { }
        #region  Method

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("ID", "Prize");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Prize");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        public DataSet ReceiveStage(string userAccount, int prizeID)
        {
            IDataParameter[] parms = new IDataParameter[]{ 
                new SqlParameter("@userAccount",userAccount),
                new SqlParameter("@prizeID",prizeID)
            };
            DataSet ds = DbHelperSQL.RunProcedure("ReceiveStage", parms, "ReceiveStage");
            return ds;
        }

        public DataSet NoticeClaimGoods(string userAccount)
        {
            IDataParameter[] parms = new IDataParameter[]{ 
                new SqlParameter("@userAccount",userAccount)
            };
            DataSet ds = DbHelperSQL.RunProcedure("NoticeClaimGoods", parms, "NoticeClaimGoods");
            return ds;
        }

        public DataSet BuyGoods(string userAccount, int prizeID)
        {
            IDataParameter[] parms = new IDataParameter[]{ 
                new SqlParameter("@userAccount",userAccount),
                new SqlParameter("@prizeID",prizeID)
            };
            DataSet ds = DbHelperSQL.RunProcedure("BuyGoodds", parms, "BuyGoodds");
            return ds;
        }


        public DataSet ExchangePrize(string account, string password, string userAccount, int prizeID, string userName, string userQQ, string userPhone, string addr)
        {
            IDataParameter[] parms = new IDataParameter[]{ 
                new SqlParameter("@account",account),
                new SqlParameter("@password",password),
                new SqlParameter("@userAccount",userAccount),
                new SqlParameter("@prizeID",prizeID),
                new SqlParameter("@UserName",userName),
                new SqlParameter("@userQQ",userQQ),
                new SqlParameter("@userPhone",userPhone),
                new SqlParameter("@Addr",addr)
            };
            DataSet ds = DbHelperSQL.RunProcedure("ExchangePrize", parms, "ExchangePrize");
            return ds;
        }


        public DataSet GetClientMyPrizeList(string userAccount, int caidanTypeID)
        {
            IDataParameter[] parms = new IDataParameter[]{ 
                new SqlParameter("@userAccount",userAccount),
                new SqlParameter("@caidanTypeID",caidanTypeID)
            };
            DataSet ds = DbHelperSQL.RunProcedure("MyPrizeList", parms, "MyPrizeList");
            return ds;
        }

        public DataSet GetClientPrizeDetail(int prizeID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select p.Image,p.Name,p.Price,case p.ExchangeType when 0 then '人民币' else p2.Name end prizeName,p.Remark from Prize p ");
            strSql.Append(" left join Prize p2 on p.ExchangeType=p2.ID where p.ID=" + prizeID);
            return DbHelperSQL.Query(strSql.ToString());
        }


        public DataSet GetClientPrizeList(string userAccount, int prizeTypeID, int caidanTypeID)
        {
            IDataParameter[] parms = new IDataParameter[]{ 
                new SqlParameter("@userAccount",userAccount),
                new SqlParameter("@prizeTypeID",prizeTypeID),
                new SqlParameter("@caidanTypeID",caidanTypeID)
            };
            DataSet ds = DbHelperSQL.RunProcedure("GetPrizeList", parms, "PrizeList");
            return ds;
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(GtsServer.Model.Prize model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Prize(");
            strSql.Append("Name,Image,CreateTime,IsUsed,Price,PrizeTypeID,SortNum,Remark,IsTaskCondition,IsShopShow,ExchangeType,PrizeCount,IsMainGold)");
            strSql.Append(" values (");
            strSql.Append("@Name,@Image,@CreateTime,@IsUsed,@Price,@PrizeTypeID,@SortNum,@Remark,@IsTaskCondition,@IsShopShow,@ExchangeType,@PrizeCount,@IsMainGold)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@Name", SqlDbType.VarChar,50),
					new SqlParameter("@Image", SqlDbType.VarChar,50),
					new SqlParameter("@CreateTime", SqlDbType.DateTime),
					new SqlParameter("@IsUsed", SqlDbType.Bit,1),
					new SqlParameter("@Price", SqlDbType.Decimal,9),
					new SqlParameter("@PrizeTypeID", SqlDbType.Int,4),
					new SqlParameter("@SortNum", SqlDbType.Int,4),
					new SqlParameter("@Remark", SqlDbType.VarChar,200),
					new SqlParameter("@IsTaskCondition", SqlDbType.Bit,1),
					new SqlParameter("@IsShopShow", SqlDbType.Bit,1),
					new SqlParameter("@ExchangeType", SqlDbType.Int,4),
					new SqlParameter("@PrizeCount", SqlDbType.Int,4),
					new SqlParameter("@IsMainGold", SqlDbType.Bit,1)};
            parameters[0].Value = model.Name;
            parameters[1].Value = model.Image;
            parameters[2].Value = model.CreateTime;
            parameters[3].Value = model.IsUsed;
            parameters[4].Value = model.Price;
            parameters[5].Value = model.PrizeTypeID;
            parameters[6].Value = model.SortNum;
            parameters[7].Value = model.Remark;
            parameters[8].Value = model.IsTaskCondition;
            parameters[9].Value = model.IsShopShow;
            parameters[10].Value = model.ExchangeType;
            parameters[11].Value = model.PrizeCount;
            parameters[12].Value = model.IsMainGold ?? false;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(GtsServer.Model.Prize model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Prize set ");
            strSql.Append("Name=@Name,");
            strSql.Append("Image=@Image,");
            strSql.Append("IsUsed=@IsUsed,");
            strSql.Append("Price=@Price,");
            strSql.Append("PrizeTypeID=@PrizeTypeID,SortNum=@SortNum,Remark=@Remark,IsTaskCondition=@IsTaskCondition,IsShopShow=@IsShopShow,ExchangeType=@ExchangeType,PrizeCount=@PrizeCount");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@Name", SqlDbType.VarChar,50),
					new SqlParameter("@Image", SqlDbType.VarChar,50),
					new SqlParameter("@IsUsed", SqlDbType.Bit,1),
					new SqlParameter("@Price", SqlDbType.Decimal,9),
					new SqlParameter("@PrizeTypeID", SqlDbType.Int,4),
					new SqlParameter("@ID", SqlDbType.Int,4),
					new SqlParameter("@SortNum", SqlDbType.Int,4),
					new SqlParameter("@Remark", SqlDbType.VarChar,200),
					new SqlParameter("@IsTaskCondition", SqlDbType.Bit,1),
					new SqlParameter("@IsShopShow", SqlDbType.Bit,1),
					new SqlParameter("@ExchangeType", SqlDbType.Int,4),
					new SqlParameter("@PrizeCount", SqlDbType.Int,4)};
            parameters[0].Value = model.Name;
            parameters[1].Value = model.Image;
            parameters[2].Value = model.IsUsed;
            parameters[3].Value = model.Price;
            parameters[4].Value = model.PrizeTypeID;
            parameters[5].Value = model.ID;
            parameters[6].Value = model.SortNum;
            parameters[7].Value = model.Remark;
            parameters[8].Value = model.IsTaskCondition;
            parameters[9].Value = model.IsShopShow;
            parameters[10].Value = model.ExchangeType;
            parameters[11].Value = model.PrizeCount;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Prize ");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string IDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Prize ");
            strSql.Append(" where ID in (" + IDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public GtsServer.Model.Prize GetModel(int ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ID,Name,Image,CreateTime,IsUsed,Price,PrizeTypeID,SortNum,Remark,IsTaskCondition,IsShopShow,ExchangeType,PrizeCount from Prize ");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            GtsServer.Model.Prize model = new GtsServer.Model.Prize();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"] != null && ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Name"] != null && ds.Tables[0].Rows[0]["Name"].ToString() != "")
                {
                    model.Name = ds.Tables[0].Rows[0]["Name"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Image"] != null && ds.Tables[0].Rows[0]["Image"].ToString() != "")
                {
                    model.Image = ds.Tables[0].Rows[0]["Image"].ToString();
                }
                if (ds.Tables[0].Rows[0]["CreateTime"] != null && ds.Tables[0].Rows[0]["CreateTime"].ToString() != "")
                {
                    model.CreateTime = DateTime.Parse(ds.Tables[0].Rows[0]["CreateTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["IsUsed"] != null && ds.Tables[0].Rows[0]["IsUsed"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["IsUsed"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsUsed"].ToString().ToLower() == "true"))
                    {
                        model.IsUsed = true;
                    }
                    else
                    {
                        model.IsUsed = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["Price"] != null && ds.Tables[0].Rows[0]["Price"].ToString() != "")
                {
                    model.Price = decimal.Parse(ds.Tables[0].Rows[0]["Price"].ToString());
                }
                if (ds.Tables[0].Rows[0]["PrizeTypeID"] != null && ds.Tables[0].Rows[0]["PrizeTypeID"].ToString() != "")
                {
                    model.PrizeTypeID = int.Parse(ds.Tables[0].Rows[0]["PrizeTypeID"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        public DataSet GetList(int pageIndex, int pageSize, int typeID = 0)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from( select p.ID,p.Name,p.Image,p.CreateTime,p.IsUsed,p.Price,p.PrizeTypeID,p.SortNum,p.Remark,p.IsTaskCondition,p.IsShopShow,p.ExchangeType,p.PrizeCount,pt.Name TypeName,case p.ExchangeType when 0 then '人民币' else p2.Name end ExchangeTypeName,ROW_NUMBER() OVER(Order by p.SortNum) as RowNumber FROM Prize p ");
            strSql.Append(" left join PrizeType pt on p.PrizeTypeID=pt.ID left join Prize p2 on p.ExchangeType=p2.ID where p.IsDelete=0 and isnull(p.IsMainGold,0)=0");
            if (typeID > 0)
            {
                strSql.Append(" and p.prizeTypeID=" + typeID + " ");
            }
            strSql.Append(" ) a where  RowNumber BETWEEN " + ((pageIndex - 1) * pageSize + 1) + " and " + pageIndex * pageSize + ";");

            strSql.Append(" select COUNT(1) from Prize where IsDelete=0 and isnull(IsMainGold,0)=0 ");
            if (typeID > 0)
            {
                strSql.Append(" and prizeTypeID=" + typeID + " ");
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        public DataSet GetPrizeName()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,Name from ");
            strSql.Append(" Prize where IsTaskCondition =1 or IsMainGold =1 ");
            strSql.Append(" order by ID");
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetPrizeName(int typeID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,Name from Prize where PrizeTypeID in (1,2,3,6," + typeID + ") and IsMainGold =0 and Price>=30 order by prizeTypeID,createTIme");
            return DbHelperSQL.Query(strSql.ToString());
        }


        public DataSet GetListByIsTaskCondition()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select p.ID,p.Name,p.Image,p.CreateTime,p.IsUsed,p.Price,p.PrizeTypeID,p.SortNum,p.Remark,p.IsTaskCondition,p.IsShopShow,p.ExchangeType,p.PrizeCount,pt.Name TypeName FROM Prize p ");
            strSql.Append(" left join PrizeType pt on p.PrizeTypeID=pt.ID where p.IsDelete=0 and (IsTaskCondition=1 or IsMainGold=1)");
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 获取商城中的物品（道具）
        /// </summary>
        /// <returns></returns>
        public DataSet GetListByIsShopShow(int typeID = 0)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select p.ID,p.Name,p.Image,p.Price,p.PrizeCount,p.Remark,case p.ExchangeType when 0 then '人民币' else p2.Name end ExchangeName ");
            strSql.Append("FROM Prize p left join Prize p2 on p.ExchangeType=p2.ID  where p.IsDelete=0 and p.IsShopShow=1 ");
            if (typeID != 0)
            {
                strSql.Append(" and p.PrizeTypeID=" + typeID);
            }
            strSql.Append(" order by  p.SortNum");
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 获取商城中的物品（道具）
        /// </summary>
        /// <returns></returns>
        public DataSet GetListByIsShopShow(string userAccount, string typeIDs = "")
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select p.ID,p.Name,p.Image,p.Price,p.PrizeCount,p.Remark,case p.ExchangeType when 0 then '人民币' else p2.Name end ExchangeName ");
            strSql.Append("FROM Prize p left join Prize p2 on p.ExchangeType=p2.ID  where p.IsDelete=0 and p.IsShopShow=1 ");
            if (!string.IsNullOrEmpty(typeIDs))
            {
                strSql.Append(" and p.PrizeTypeID in " + typeIDs);
            }
            strSql.Append(" order by p.PrizeTypeID, p.SortNum;");
            strSql.Append(" select up.Prizecount Prizecount from [User] u left join UserPrize up on u.UserInfoID = up.UserInfoID left join Prize p on up.PrizeID = p.ID  where p.PrizeTypeID =2 and p.IsMainGold =1 and u.UserAccount ='" + userAccount + "' ");
            return DbHelperSQL.Query(strSql.ToString());
        }

        //public DataSet GetConditionPrize()
        //{ 

        //}

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,Name,Image,CreateTime,IsUsed,Price,PrizeTypeID,SortNum,Remark,IsTaskCondition,IsShopShow,ExchangeType,PrizeCount ");
            strSql.Append(" FROM Prize ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by SortNum");
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetListIDNames(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,Name ");
            strSql.Append(" FROM Prize ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by SortNum");
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" ID,Name,Image,CreateTime,IsUsed,Price,PrizeTypeID,SortNum,Remark,IsTaskCondition,IsShopShow,ExchangeType,PrizeCount ");
            strSql.Append(" FROM Prize ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "Prize";
            parameters[1].Value = "ID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  Method
    }
}

